﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Oracle.DataAccess.Client;

namespace CrmApplication.Models
{    
    public class UserModels
	{
		#region RegionMappingUser
		
		public decimal Id { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
        public string Username { get; set; }
        public string Password { get; set; }
        public decimal Available { get; set; }
        public string AccessLevel { get; set; }
        public string Email { get; set; }

		#endregion

		#region RegionOracleConnect

		private static OracleConnection connect;

       /// <summary>
       /// database connection string
       /// </summary>
        static UserModels()
        {
            string connString = System.Configuration.ConfigurationManager.ConnectionStrings["HomeOrcl"].ConnectionString;
            connect = new OracleConnection(connString);
        }

		#endregion

		#region RegionUser
		/// <summary>
        /// select user by username and password(used to verify data for login)
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static UserModels GetUser(UserModels user)
        {
            string selectString = "select id,username,password,acces_level"+
                                    " from users where username =:userName and password=:password";            

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":userName", user.Username);
            loginComm.Parameters.Add(":password", user.Password);            

            UserModels result = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new UserModels();
                        result.Id = sdr.GetDecimal(0);
                        result.Username = sdr.GetString(1);
                        result.Password = sdr.GetString(2);
                        result.AccessLevel= sdr.GetString(3);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        
        /// <summary>
        /// verify if username exists
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static UserModels CheckUsername(UserModels user)
        {
            string selectString = "SELECT ID,USERNAME from USERS where USERNAME =:userName ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            loginComm.Parameters.Add(":userName", user.Username);

            UserModels result = null;
            
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new UserModels();
                        result.Id = sdr.GetDecimal(0);
                        result.Username = sdr.GetString(1);                        
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        
        /// <summary>
        /// select user info by id(used for edit)
        /// </summary>
        /// <param name="idUser"></param>
        /// <returns>user object</returns>
        public static List<UserModels> GetUserById(decimal idUser)
        {
            string selectString = "SELECT id,username,password,acces_level,available,email,first_name,last_name" +
                                        " from USERS where id= :id";
           
            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;
            
            loginComm.Parameters.Add(":id", idUser);

            List<UserModels> result = new List<UserModels>();
            UserModels result1 = null;

            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new UserModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.Username = sdr.GetString(1);
                        result1.Password = sdr.GetString(2);
                        result1.AccessLevel = sdr.GetString(3);
                        result1.Available = sdr.GetDecimal(4);
                        result1.Email = sdr.GetString(5);
                        result1.FirstName = sdr.GetString(6);
                        result1.LastName = sdr.GetString(7);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// select all users
        /// </summary>
        /// <returns>list of user objects</returns>
        public static List<UserModels> GetUsers()
        {
            string selectString = "SELECT ID,first_name,last_name,username,available,acces_level,email from USERS ";

            OracleCommand loginComm = new OracleCommand(selectString);
            loginComm.Connection = connect;

            List<UserModels> result = new List<UserModels>();
            UserModels result1 = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = loginComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result1 = new UserModels();
                        result1.Id = sdr.GetDecimal(0);
                        result1.FirstName = sdr.GetString(1);
                        result1.LastName = sdr.GetString(2);
                        result1.Username = sdr.GetString(3);
                        result1.Available = sdr.GetDecimal(4);
                        result1.AccessLevel = sdr.GetString(5);
                        result1.Email = sdr.GetString(6);
                        result.Add(result1);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// change selected user password
        /// </summary>
        /// <param name="password"></param>
        /// <param name="userId"></param>
        /// <returns></returns>
        public static UserModels ChangePassword(string password, decimal userId)
        {
            string selectString = "update users set password=:password where id=:userId";

            OracleCommand updateComm = new OracleCommand(selectString);
            updateComm.Connection = connect;
            
            updateComm.Parameters.Add(":password", password);
            updateComm.Parameters.Add(":idUser", userId);

            UserModels result = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = updateComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new UserModels();
                        result.Password = sdr.GetString(0);                        
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        
        /// <summary>
        /// edit user info(update selected user database records) exept for password
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static UserModels EditUser(UserModels user)
        {
            string selectString = "update users set last_name=:lastName,first_name=:firstName,email=:email,userName=:userName,"+
                                    "acces_level=:accessLevel,available=:available where id=:userId";

            OracleCommand updateComm = new OracleCommand(selectString);
            updateComm.Connection = connect;
            updateComm.Parameters.Add(":lastName", user.LastName);
            updateComm.Parameters.Add(":firstName", user.FirstName);
            updateComm.Parameters.Add(":email", user.Email);
            updateComm.Parameters.Add(":userName", user.Username);
            updateComm.Parameters.Add(":accessLevel", user.AccessLevel);
            updateComm.Parameters.Add(":available", user.Available);
            updateComm.Parameters.Add(":idUser", user.Id);

            UserModels result = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = updateComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new UserModels();
                        result.LastName = sdr.GetString(0);
                        result.FirstName = sdr.GetString(1);
                        result.Email = sdr.GetString(2);
                        result.Username = sdr.GetString(3);
                        result.AccessLevel = sdr.GetString(4);
                        result.Available = sdr.GetDecimal(5);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }
        
        /// <summary>
        /// edit user info including password
        /// </summary>
        /// <param name="user"></param>
        /// <returns></returns>
        public static UserModels EditUserPass(UserModels user)
        {
            string selectString = "UPDATE Users set last_name = :lastName, first_name=:firstName,email=:email" +
                                    ",username=:userName,acces_level=:accessLevel,available=:available" +
                                    ",password=:password " +
                                    "where id=:idUser";

            OracleCommand updateComm = new OracleCommand(selectString);
            updateComm.Connection = connect;
            updateComm.Parameters.Add(":lastName", user.LastName);
            updateComm.Parameters.Add(":firstName", user.FirstName);
            updateComm.Parameters.Add(":email", user.Email);
            updateComm.Parameters.Add(":userName", user.Username);
            updateComm.Parameters.Add(":accessLevel", user.AccessLevel);
            updateComm.Parameters.Add(":available", user.Available);
            updateComm.Parameters.Add(":password", user.Password);
            updateComm.Parameters.Add(":idUser", user.Id);

            UserModels result = null;
            try
            {
                connect.Open();
                using (OracleDataReader sdr = updateComm.ExecuteReader())
                {
                    while (sdr.Read())
                    {
                        result = new UserModels();
                        result.LastName = sdr.GetString(0);
                        result.FirstName = sdr.GetString(1);
                        result.Email = sdr.GetString(2);
                        result.Username = sdr.GetString(3);
                        result.AccessLevel = sdr.GetString(4);
                        result.Available = sdr.GetDecimal(5);
                        result.Password = sdr.GetString(6);
                    }
                }
            }
            finally
            {
                connect.Close();
            }
            return result;
        }

        /// <summary>
        /// add new user
        /// </summary>
        /// <param name="user"></param>
        public static void AddUser(UserModels user)
        {
            string insert = "INSERT INTO users(last_name,first_name,username,email,acces_level,available,password)" +
                            "VALUES (:lastName,:firtsName,:userName,:email,:accessLevel,:available,:password)";

            OracleCommand usercomm = new OracleCommand(insert);
            usercomm.Parameters.Add("lastName", user.LastName);
            usercomm.Parameters.Add("firstName", user.FirstName);
            usercomm.Parameters.Add("userName", user.Username);
            usercomm.Parameters.Add("email", user.Email);
            usercomm.Parameters.Add("accessLevel", user.AccessLevel);
            usercomm.Parameters.Add("available", user.Available);
            usercomm.Parameters.Add("password", user.Password);

            usercomm.Connection = connect;

            try
            {
                connect.Open();
                usercomm.ExecuteNonQuery();
            }
            finally
            {
                connect.Close();
            }
        }

		#endregion

		#region RegionJsonArray
		/// <summary>
        /// creats array for all users list(used with json)
        /// </summary>
        /// <returns></returns>
        public static UserContainer GetUserArray()
        {
            UserModels[] data = new UserModels[20];           
            data = UserModels.GetUsers().ToArray();

            UserContainer container = new UserContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
        }
        
        /// <summary>
        /// creats array for selected user by id(used with json)
        /// </summary>
        /// <param name="idUser"></param>
        /// <returns></returns>
        public static UserContainer GetUserArrayById(decimal idUser)
        {
            UserModels[] data = new UserModels[20];           
            data = UserModels.GetUserById(idUser).ToArray();

            UserContainer container = new UserContainer()
            {
                Records = data,
                Count = data.Length
            };

            return container;
		}
		#endregion
	}


    public class UserContainer
    {
        public UserModels[] Records { get; set; }
        public int Count { get; set; }
    }

}